package com.hlw.hr_man.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hlw.hr_man.Model.Overtime;
import com.hlw.hr_man.dao.OverTimeDao;
import com.hlw.hr_man.util.JDBCUtil;

/**
 * 
 * @ClassName: OvertimeDaoImpl
 * @Description: 员工加班实现类
 * @author 唐明
 * @date 2016年9月12日 上午10:07:03
 *
 */
public class OvertimeDaoImpl implements OverTimeDao {

	@Override
	public List<Overtime> selectEmployeeOvertimeByEid(int eid) {
		StringBuffer sql = new StringBuffer("select hr_employee.e_number,hr_employee.e_name,hr_overtime.ot_starttime,");
		sql.append("hr_overtime.ot_endtime,hr_overtime.ot_describe from hr_overtime,hr_employee where ");
		sql.append("hr_overtime.e_id=hr_employee.e_id and hr_employee.e_id=?");
		Connection conn = JDBCUtil.getConnection();
		List<Overtime> ots = new ArrayList<Overtime>();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, eid);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				Overtime ot = new Overtime();
				ot.setE_number(rs.getString("e_number"));
				ot.setE_name(rs.getString("e_name"));
				ot.setOt_starttime(rs.getString("ot_starttime"));
				ot.setOt_endtime(rs.getString("ot_endtime"));
				ot.setOt_describe(rs.getString("ot_describe"));
				ots.add(ot);
			}
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ots;
	}

	@Override
	public List<Overtime> selectEmployeeOvertimeByEid(int eid, int start, int pageSize) {
		StringBuffer sql = new StringBuffer("select hr_employee.e_number,hr_employee.e_name,hr_overtime.ot_starttime,");
		sql.append("hr_overtime.ot_endtime,hr_overtime.ot_describe from hr_overtime,hr_employee where ");
		sql.append("hr_overtime.e_id=hr_employee.e_id and hr_employee.e_id=? limit ?,?");
		Connection conn = JDBCUtil.getConnection();
		List<Overtime> ots = new ArrayList<Overtime>();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, eid);
			pstmt.setInt(2, start);
			pstmt.setInt(3, pageSize);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				Overtime ot = new Overtime();
				ot.setE_number(rs.getString("e_number"));
				ot.setE_name(rs.getString("e_name"));
				ot.setOt_starttime(rs.getString("ot_starttime"));
				ot.setOt_endtime(rs.getString("ot_endtime"));
				ot.setOt_describe(rs.getString("ot_describe"));
				ots.add(ot);
			}
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ots;
	}

	@Override
	public int selectEmployeeOvertimeCount(int eid) {
		StringBuffer sql = new StringBuffer("select count(ot_id) ot_count from hr_overtime where e_id=?");
		Connection conn = JDBCUtil.getConnection();
		int ot_count = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, eid);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				ot_count = rs.getInt("ot_count");
			}
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ot_count;
	}

	@Override
	public List<Overtime> selectEmployeeOvertimeBySearch(String searchType, String searchContent, int start,
			int pageSize) {
		StringBuffer sql = new StringBuffer("select hr_employee.e_number,hr_employee.e_name,hr_overtime.ot_starttime,");
		sql.append("hr_overtime.ot_endtime,hr_overtime.ot_describe from hr_overtime,hr_employee where ");
		sql.append("hr_overtime.e_id=hr_employee.e_id");
		if (searchType.equals("员工姓名")) {
			sql.append(" and e_name like ?");
		}
		if (searchType.equals("员工编号")) {
			sql.append(" and e_number like ?");
		}
		sql.append(" order by hr_employee.e_number");
		sql.append(" limit ?,?");
		Connection conn = JDBCUtil.getConnection();
		List<Overtime> ots = new ArrayList<Overtime>();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			if (searchType.equals("查找所有")) {
				pstmt.setInt(1, start);
				pstmt.setInt(2, pageSize);
			} else {
				pstmt.setString(1, "%"+searchContent+"%");
				pstmt.setInt(2, start);
				pstmt.setInt(3, pageSize);
			}
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				Overtime ot = new Overtime();
				ot.setE_number(rs.getString("e_number"));
				ot.setE_name(rs.getString("e_name"));
				ot.setOt_starttime(rs.getString("ot_starttime"));
				ot.setOt_endtime(rs.getString("ot_endtime"));
				ot.setOt_describe(rs.getString("ot_describe"));
				ots.add(ot);
			}
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ots;
	}

	@Override
	public int selectEmployeeOvertimeCount(String searchType, String searchContent) {
		StringBuffer sql = new StringBuffer("select count(ot_id) ot_count from hr_overtime,hr_employee where hr_overtime.e_id=hr_employee.e_id ");
		if (searchType.equals("员工姓名")) {
			sql.append("and e_name like ?");
		}
		if (searchType.equals("员工编号")) {
			sql.append("and e_number like ?");
		}
		Connection conn = JDBCUtil.getConnection();
		int ot_count = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			if (!searchType.equals("查找所有")){
				pstmt.setString(1, "%"+searchContent+"%");
			}
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				ot_count = rs.getInt("ot_count");
			}
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ot_count;
	}

	@Override
	public int insertOvertime(Overtime ot) {
		StringBuffer sql = new StringBuffer("insert into hr_overtime values(?,?,?,?,?)");
		Connection conn = JDBCUtil.getConnection();
		int temp = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, ot.getOt_id());
			pstmt.setInt(2, ot.getE_id());
			pstmt.setString(3, ot.getOt_starttime());
			pstmt.setString(4, ot.getOt_endtime());
			pstmt.setString(5, ot.getOt_describe());
			temp = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return temp;
	}

	@Override
	public int deleteOvertimeByEid(int e_id) {
		StringBuffer sql = new StringBuffer("delete from hr_overtime where e_id=?");
		Connection conn = JDBCUtil.getConnection();
		int temp = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, e_id);
			temp = pstmt.executeUpdate();
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return temp;
	}

}
